SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Invoice_VSP]( [Inv_VSPID] [bigint] IDENTITY(1,1) NOT NULL, [Inv_VSPDate] [datetime] NOT NULL, [Inv_VSPVNo] [varchar](16) NOT NULL, [SessionID] [smallint] NOT NULL, [Remarks] [varchar](150) NULL, [ReadOnly] [bit] NOT NULL CONSTRAINT [DF_Invoice_VSP_ReadOnly] DEFAULT ((0)), [LoginID] [int] NOT NULL, [HostName] [varchar](50) NULL CONSTRAINT [DF_Invoice_VSP_HostName] DEFAULT (host_name()), [EntryDateTime] [datetime] NULL CONSTRAINT [DF_Invoice_VSP_EntryDateTime] DEFAULT (getdate()), [ModifyID] [int] NULL, [ModifyHostName] [varchar](50) NULL, [ModifyDateTime] [datetime] NULL, [BEDate] [datetime] NULL, [BENo] [varchar](50) NULL, [Currency] [varchar](50) NOT NULL, [ContainerNo] [varchar](200) NULL, [NoOfContainers] [numeric](15, 0) NOT NULL CONSTRAINT [DF_Invoice_VSP_NoOfContainers] DEFAULT ((0)), [SumLQTY] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSP_SumLQTY] DEFAULT ((0)), [SumSQTY] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSP_SumSQTY] DEFAULT ((0)), [SumDQTY] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSP_SumDQTY] DEFAULT ((0)), [SumTQTY] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSP_SumTQTY] DEFAULT ((0)), [SumLQTY2] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSP_SumLQTY2] DEFAULT ((0)), [SumGross] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSP_SumGross] DEFAULT ((0)), [SumFreight] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSP_SumFreight] DEFAULT ((0)), [SumOtCharges] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSP_SumOtCharges] DEFAULT ((0)), [SumComission] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSP_SumComission] DEFAULT ((0)), [SumNetSale] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSP_SumNetSale] DEFAULT ((0)), CONSTRAINT [PK_Invoice_VSP] PRIMARY KEY CLUSTERED ( [Inv_VSPID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Invoice_VSParty] Script Date: 04/24/2021 16:38:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Invoice_VSParty]( [inv_VSPartyID] [bigint] IDENTITY(1,1) NOT NULL, [inv_VSPID] [bigint] NOT NULL, [DebitID] [int] NULL, [SubID] [int] NULL, [ItemID] [smallint] NULL, [VarietyID] [smallint] NULL, [SQTY] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSParty_SQTY] DEFAULT ((0)), [DQTY] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSParty_DQTY] DEFAULT ((0)), [TQTY] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSParty_TQTY] DEFAULT ((0)), [LQTY] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSParty_LQTY] DEFAULT ((0)), [Gross] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSParty_Gross] DEFAULT ((0)), [Freight] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSParty_Freight] DEFAULT ((0)), [OtCharges] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSParty_OtCharges] DEFAULT ((0)), [Comission] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSParty_Comission] DEFAULT ((0)), [NetSale] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSParty_NetSale] DEFAULT ((0)), [StatusID] [smallint] NULL, [Type] [tinyint] NOT NULL CONSTRAINT [DF_Invoice_VSParty_Type] DEFAULT ((0)), [Remarks] [varchar](100) NULL, [ContainerNo] [varchar](20) NULL, [RefVNo] [varchar](20) NULL, [SaleDate] [datetime] NULL, CONSTRAINT [PK_Invoice_VSParty] PRIMARY KEY CLUSTERED ( [inv_VSPartyID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Invoice_VSPitem] Script Date: 04/24/2021 16:38:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Invoice_VSPitem]( [inv_VSPItemID] [bigint] IDENTITY(1,1) NOT NULL, [inv_VSPID] [bigint] NOT NULL, [SubTypeID] [smallint] NULL, [DTypeID] [smallint] NULL, [QTY] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSPitem_QTY] DEFAULT ((0)), [Amount] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSPitem_Amount] DEFAULT ((0)), [ExRate] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSPitem_ExRate] DEFAULT ((0)), [NetAmount] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_VSPitem_NetAmount] DEFAULT ((0)), [Type] [tinyint] NOT NULL CONSTRAINT [DF_Invoice_VSPitem_Type] DEFAULT ((0)), [ContainerNo] [varchar](20) NULL, CONSTRAINT [PK_Invoice_VSPitem] PRIMARY KEY CLUSTERED ( [inv_VSPItemID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF ---------------------------------------------------------------------------------------------- ALTER VIEW [dbo].[vw_Ledger] AS SELECT '01' + CONVERT(Varchar, dbo.Voucher.VoucherID) AS VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, dbo.VoucherDetails.AccountNo, dbo.Accounts.AccountTitle, dbo.Accounts.ParentAccount, CASE WHEN dbo.VoucherDetails.DrOrCr = 'Dr' THEN dbo.VoucherDetails.Amount ELSE 0 END AS Debit, CASE WHEN dbo.VoucherDetails.DrOrCr = 'Cr' THEN dbo.VoucherDetails.Amount ELSE 0 END AS Credit, dbo.VoucherDetails.DrOrCr, CASE WHEN dbo.VoucherDetails.AccountNo = dbo.VoucherDetails.SubPartyID THEN dbo.VoucherDetails.Remarks ELSE dbo.VoucherDetails.Remarks + ' ' + ISNULL(LEFT(Accounts_1.AccountTitle, 15), '') END AS Remarks, dbo.VoucherDetails.Qty, dbo.Voucher.NoOfContainers, dbo.Accounts.Currency, CASE WHEN dbo.VoucherDetails.AccountNo = dbo.VoucherDetails.SubPartyID THEN dbo.VoucherDetails.Remarks ELSE dbo.VoucherDetails.Remarks + ' ' + ISNULL(LEFT(Accounts_1.AccountTitle, 15), '') END AS mRemarks, '' AS ContainerNo, dbo.VoucherDetails.SubPartyID, Accounts_1.AccountTitle AS SubPartyName, dbo.Voucher.VoucherDate AS BLDate FROM dbo.Voucher INNER JOIN dbo.VoucherDetails ON dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN dbo.Accounts ON dbo.VoucherDetails.AccountNo = dbo.Accounts.AccountNo LEFT OUTER JOIN dbo.Accounts AS Accounts_1 ON dbo.VoucherDetails.SubPartyID = Accounts_1.AccountNo UNION ALL SELECT '02' + CONVERT(Varchar, dbo.Invoice_Agent.Inv_AgentID) AS VoucherID, dbo.Invoice_AgentDetail.SaleDate, dbo.Invoice_Agent.Inv_AgentVNo, dbo.Invoice_AgentDetail.AgentID, Accounts_7.AccountTitle, Accounts_7.ParentAccount, 0 AS Debit, dbo.Invoice_AgentDetail.NetAmount AS Credit, 'Cr' AS DrOrCr, dbo.Invoice_AgentDetail.Remarks, dbo.Invoice_AgentDetail.QTY, dbo.Invoice_Agent.NoOfContainers, Accounts_7.Currency, dbo.Invoice_Agent.Remarks + ' ' + dbo.Invoice_Agent.BENo AS mRemarks, ISNULL(dbo.Invoice_Agent.ContainerNo, '') AS ContainerNo, NULL AS SubPartyID, NULL AS SubPartyName, dbo.Invoice_AgentDetail.SaleDate AS BLDate FROM dbo.Invoice_Agent LEFT OUTER JOIN dbo.Invoice_AgentDetail LEFT OUTER JOIN dbo.Groups ON dbo.Invoice_AgentDetail.DTypeID = dbo.Groups.GroupID LEFT OUTER JOIN dbo.Accounts AS Accounts_7 ON dbo.Invoice_AgentDetail.AgentID = Accounts_7.AccountNo ON dbo.Invoice_Agent.Inv_AgentID = dbo.Invoice_AgentDetail.inv_AgentID UNION ALL SELECT '03' + CONVERT(Varchar, dbo.Invoice_Air.Inv_AirID) AS VoucherID, dbo.Invoice_Air.AWBDate, dbo.Invoice_Air.Inv_AirVNo, dbo.Invoice_Air.ConsigneeID, Accounts_6.AccountTitle, Accounts_6.ParentAccount, dbo.Invoice_Air.NetInvoice AS Debit, 0 AS Credit, 'Dr' AS DrOrCr, dbo.Invoice_AirItem.DRemarks + ' ' + dbo.Varietys.VarietyName AS Expr1, dbo.Invoice_Air.SumQTY, 0 AS Expr2, Accounts_6.Currency, dbo.Invoice_Air.Remarks AS mRemarks, '' AS ContainerNo, NULL AS SubPartyID, NULL AS SubPartyName, dbo.Invoice_Air.AWBDate AS BLDate FROM dbo.Varietys LEFT OUTER JOIN dbo.Invoice_AirItem ON dbo.Varietys.VarietyID = dbo.Invoice_AirItem.VarietyID RIGHT OUTER JOIN dbo.Accounts AS Accounts_6 RIGHT OUTER JOIN dbo.Invoice_Air ON Accounts_6.AccountNo = dbo.Invoice_Air.ConsigneeID ON dbo.Invoice_AirItem.inv_AirID = dbo.Invoice_Air.Inv_AirID UNION ALL SELECT '04' + CONVERT(Varchar, dbo.Invoice_CTN.Inv_CTNID) AS VoucherID, dbo.Invoice_CTN.Inv_CTNDate, dbo.Invoice_CTN.Inv_CTNVNo, dbo.Invoice_CTNitem.PartyID, Accounts_5.AccountTitle, Accounts_5.ParentAccount, 0 AS Debit, dbo.Invoice_CTNitem.CrAmount, 'Cr' AS Expr1, dbo.Invoice_CTNitem.DRemarks + ' ' + Varietys_4.VarietyName AS Remarks, dbo.Invoice_CTNitem.Bot AS QTY, dbo.Invoice_CTNitem.Sheet AS NoofContainers, Accounts_5.Currency, dbo.Invoice_CTN.Remarks AS mRemarks, '' AS ContainerNo, NULL AS SubPartyID, NULL AS SubPartyName, dbo.Invoice_CTN.Inv_CTNDate AS BLDate FROM dbo.Varietys AS Varietys_4 RIGHT OUTER JOIN dbo.Invoice_CTNitem ON Varietys_4.VarietyID = dbo.Invoice_CTNitem.VarietyID LEFT OUTER JOIN dbo.Accounts AS Accounts_5 ON dbo.Invoice_CTNitem.PartyID = Accounts_5.AccountNo RIGHT OUTER JOIN dbo.Invoice_CTN ON dbo.Invoice_CTNitem.Inv_CTNID = dbo.Invoice_CTN.Inv_CTNID UNION ALL SELECT '05' + CONVERT(Varchar, dbo.Invoice_PUR.Inv_PURID) AS VoucherID, dbo.Invoice_PUR.Inv_PURDate, dbo.Invoice_PUR.Inv_PURVNo, dbo.Invoice_PURitem.PartyID, Accounts_4.AccountTitle, Accounts_4.ParentAccount, 0 AS Debit, dbo.Invoice_PURitem.Amount, 'Cr' AS DrOrCr, dbo.Invoice_PURitem.DRemarks + ' ' + Varietys_3.VarietyName AS Remarks, dbo.Invoice_PURitem.Qty, 0 AS Credit, Accounts_4.Currency, dbo.Invoice_PUR.Remarks AS mRemarks, '' AS ContainerNo, NULL AS SubPartyID, NULL AS SubPartyName, dbo.Invoice_PUR.Inv_PURDate AS BLDate FROM dbo.Invoice_PURitem LEFT OUTER JOIN dbo.Varietys AS Varietys_3 ON dbo.Invoice_PURitem.VarietyID = Varietys_3.VarietyID LEFT OUTER JOIN dbo.Accounts AS Accounts_4 ON dbo.Invoice_PURitem.PartyID = Accounts_4.AccountNo RIGHT OUTER JOIN dbo.Invoice_PUR ON dbo.Invoice_PURitem.Inv_PURID = dbo.Invoice_PUR.Inv_PURID UNION ALL SELECT '06' + CONVERT(Varchar, Invoice_PUR_1.Inv_PURID) AS VoucherID, Invoice_PUR_1.Inv_PURDate, Invoice_PUR_1.Inv_PURVNo, Invoice_PURitem_1.DlrID, Accounts_4.AccountTitle, Accounts_4.ParentAccount, 0 AS Debit, Invoice_PURitem_1.Amount, 'Cr' AS DrOrCr, Invoice_PURitem_1.DRemarks + ' ' + Varietys_2.VarietyName AS Remarks, Invoice_PURitem_1.Qty, 0 AS Credit, Accounts_4.Currency, Invoice_PUR_1.Remarks AS mRemakrs, '' AS ContainerNo, NULL AS SubPartyID, NULL AS SubPartyName, Invoice_PUR_1.Inv_PURDate AS BLDate FROM dbo.Invoice_PURitem AS Invoice_PURitem_1 LEFT OUTER JOIN dbo.Varietys AS Varietys_2 ON Invoice_PURitem_1.VarietyID = Varietys_2.VarietyID LEFT OUTER JOIN dbo.Accounts AS Accounts_4 ON Invoice_PURitem_1.DlrID = Accounts_4.AccountNo RIGHT OUTER JOIN dbo.Invoice_PUR AS Invoice_PUR_1 ON Invoice_PURitem_1.Inv_PURID = Invoice_PUR_1.Inv_PURID UNION ALL SELECT '07' + CONVERT(Varchar, dbo.Invoice_SSP.Inv_SSPID) AS VoucherID, dbo.Invoice_SSP.Inv_SSPDate, dbo.Invoice_SSP.Inv_SSPVNo, dbo.Invoice_SSParty.DebitID, Accounts_2.AccountTitle, Accounts_2.ParentAccount, CASE WHEN dbo.Invoice_SSParty.NetSale >= 0 THEN dbo.Invoice_SSParty.NetSale ELSE 0 END AS Debit, CASE WHEN dbo.Invoice_SSParty.NetSale < 0 THEN ABS(dbo.Invoice_SSParty.NetSale) ELSE 0 END AS Credit, CASE WHEN dbo.Invoice_SSParty.NetSale >= 0 THEN 'Dr' ELSE 'Cr' END AS DrOrCr, CASE WHEN dbo.Invoice_SSParty.DebitID = dbo.Invoice_SSParty.SubID THEN dbo.Invoice_SSParty.Remarks + ' ' + ISNULL(dbo.Invoice_SSParty.ContainerNo, '') + ' ' + ISNULL(Varietys_1.VarietyName, '') ELSE dbo.Invoice_SSParty.Remarks + ' ' + ISNULL(dbo.Invoice_SSParty.ContainerNo, '') + ' ' + ISNULL(Varietys_1.VarietyName, '') + ' ' + LEFT(Accounts_3.AccountTitle, 15) END AS Remarks, dbo.Invoice_SSParty.TQTY, dbo.Invoice_SSP.NoOfContainers, Accounts_2.Currency, dbo.Invoice_SSP.Remarks + ' ' + ISNULL(dbo.Invoice_SSParty.ContainerNo, '') + ' ' + dbo.Items.ItemName AS mRemarks, ISNULL(dbo.Invoice_SSParty.ContainerNo, '') AS ContainerNo, dbo.Invoice_SSParty.SubID, Accounts_3.AccountTitle AS SubPartyName, dbo.Invoice_Bank.BLDate FROM dbo.Invoice_Bank RIGHT OUTER JOIN dbo.Invoice_SSP ON dbo.Invoice_Bank.BLNo = dbo.Invoice_SSP.BENo LEFT OUTER JOIN dbo.Invoice_SSParty LEFT OUTER JOIN dbo.Accounts AS Accounts_3 ON dbo.Invoice_SSParty.SubID = Accounts_3.AccountNo LEFT OUTER JOIN dbo.Items ON dbo.Invoice_SSParty.ItemID = dbo.Items.ItemID LEFT OUTER JOIN dbo.Varietys AS Varietys_1 ON dbo.Invoice_SSParty.VarietyID = Varietys_1.VarietyID LEFT OUTER JOIN dbo.Accounts AS Accounts_2 ON dbo.Invoice_SSParty.DebitID = Accounts_2.AccountNo ON dbo.Invoice_SSP.Inv_SSPID = dbo.Invoice_SSParty.inv_SSPID UNION ALL SELECT '08' + CONVERT(Varchar, dbo.Invoice_VSP.Inv_VSPID) AS VoucherID, dbo.Invoice_VSP.Inv_VSPDate, dbo.Invoice_VSP.Inv_VSPVNo, dbo.Invoice_VSParty.DebitID, Accounts_2.AccountTitle, Accounts_2.ParentAccount, CASE WHEN dbo.Invoice_VSParty.NetSale >= 0 THEN dbo.Invoice_VSParty.NetSale ELSE 0 END AS Debit, CASE WHEN dbo.Invoice_VSParty.NetSale < 0 THEN ABS(dbo.Invoice_VSParty.NetSale) ELSE 0 END AS Credit, CASE WHEN dbo.Invoice_VSParty.NetSale >= 0 THEN 'Dr' ELSE 'Cr' END AS DrOrCr, CASE WHEN dbo.Invoice_VSParty.DebitID = dbo.Invoice_VSParty.SubID THEN dbo.Invoice_VSParty.Remarks + ' ' + ISNULL(dbo.Invoice_VSParty.ContainerNo, '') + ' ' + ISNULL(Varietys_1.VarietyName, '') ELSE dbo.Invoice_VSParty.Remarks + ' ' + ISNULL(dbo.Invoice_VSParty.ContainerNo, '') + ' ' + ISNULL(Varietys_1.VarietyName, '') + ' ' + LEFT(Accounts_3.AccountTitle, 15) END AS Expr1, dbo.Invoice_VSParty.TQTY, dbo.Invoice_VSP.NoOfContainers, Accounts_2.Currency, dbo.Invoice_VSP.Remarks + ' ' + ISNULL(dbo.Invoice_VSParty.ContainerNo, '') + ' ' + Items_1.ItemName AS mRemarks, ISNULL(dbo.Invoice_VSParty.ContainerNo, '') AS ContainerNo, dbo.Invoice_VSParty.SubID, Accounts_3.AccountTitle AS SubPartyName, Invoice_Bank_1.BLDate FROM dbo.Invoice_Bank AS Invoice_Bank_1 RIGHT OUTER JOIN dbo.Invoice_VSP ON Invoice_Bank_1.BLNo = dbo.Invoice_VSP.BENo LEFT OUTER JOIN dbo.Invoice_VSParty LEFT OUTER JOIN dbo.Accounts AS Accounts_3 ON dbo.Invoice_VSParty.SubID = Accounts_3.AccountNo LEFT OUTER JOIN dbo.Items AS Items_1 ON dbo.Invoice_VSParty.ItemID = Items_1.ItemID LEFT OUTER JOIN dbo.Varietys AS Varietys_1 ON dbo.Invoice_VSParty.VarietyID = Varietys_1.VarietyID LEFT OUTER JOIN dbo.Accounts AS Accounts_2 ON dbo.Invoice_VSParty.DebitID = Accounts_2.AccountNo ON dbo.Invoice_VSP.Inv_VSPID = dbo.Invoice_VSParty.inv_VSPID UNION ALL SELECT '09' + CONVERT(Varchar, dbo.Invoice_Trans.Inv_TransID) AS VoucherID, dbo.Invoice_TransDetail.SaleDate, dbo.Invoice_Trans.Inv_TransVNo, dbo.Invoice_TransDetail.TransID, Accounts_1.AccountTitle AS SubPartyName, Accounts_1.ParentAccount, 0 AS Debit, dbo.Invoice_TransDetail.NetFreight, 'Cr' AS DrOrCr, dbo.Invoice_TransDetail.Remarks, 0 AS QTY, dbo.Invoice_Trans.NoOfContainers, Accounts_1.Currency, dbo.Invoice_Trans.Remarks AS mRemarks, '' AS Container, NULL AS SubPartyID, NULL AS Expr1, dbo.Invoice_TransDetail.SaleDate AS Expr2 FROM dbo.Invoice_TransDetail RIGHT OUTER JOIN dbo.Invoice_Trans ON dbo.Invoice_TransDetail.inv_TransID = dbo.Invoice_Trans.Inv_TransID LEFT OUTER JOIN dbo.Accounts AS Accounts_1 ON dbo.Invoice_TransDetail.TransID = Accounts_1.AccountNo